﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using motelmanagement.common;
using System.Data;
using System.Data.OleDb;
namespace motelmanagement.dao
{
    class ServiceDetailDAO
    {
        SqlUtil sqlUtil = new SqlUtil();
        OleDbCommand cmd = null;
        private string strSql = null;

        public DataTable getAllServiceDetail() {
            strSql = "SELECT Service_detail.service_detail_id, Service_detail.service_id";
            strSql+=" , Service.service_name, Service_detail.service_Quantity, Service_detail.hire_detail_id";
            strSql+=" FROM Hire_detail INNER JOIN (Service INNER JOIN Service_detail ON Service.service_id";
            strSql+= "Service_detail.service_id) ON Hire_detail.hire_detail_id = Service_detail.hire_detail_id";
            cmd = new OleDbCommand(strSql);
            sqlUtil.Load(cmd);
            return sqlUtil;
        }

        public DataTable getServiceDetail(int id)
        {
            strSql = "SELECT Service_detail.service_detail_id, Service_detail.service_id";
            strSql += " , Service.service_name, Service_detail.service_Quantity, Service_detail.hire_detail_id";
            strSql += " FROM Hire_detail INNER JOIN (Service INNER JOIN Service_detail ON Service.service_id";
            strSql += "Service_detail.service_id) ON Hire_detail.hire_detail_id = Service_detail.hire_detail_id";
            strSql += " Where Service_detail.service_detail_id = @id";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("id",SqlDbType.Int).Value=id;
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        public void insertServiceDetail(int svId,int svQuantity,int hireId)
        {
            strSql = "Insert Into Service_detail(service_id,service_Quantity, hire_detail_id)";
            strSql += " Values(@svid,@svq,@hireid)";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("svid", SqlDbType.Int).Value = svId;
            cmd.Parameters.Add("svq", SqlDbType.Int).Value = svQuantity;
            cmd.Parameters.Add("hireid", SqlDbType.Int).Value = hireId;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        public void updateServiceDetail(int id,int svId, int svQuantity, int hireId)
        {
            strSql = "Update Service_detail Set service_id=@svid,service_Quantity=@svq, hire_detail_id=@hireid";
            strSql += " Where service_detail_id=@id";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("svid", SqlDbType.Int).Value = svId;
            cmd.Parameters.Add("svq", SqlDbType.Int).Value = svQuantity;
            cmd.Parameters.Add("hireid", SqlDbType.Int).Value = hireId;
            cmd.Parameters.Add("id", SqlDbType.Int).Value = id;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        public void deleteServiceDetail(int id)
        {
            strSql = "Delete From Service_detail Where service_detail_id=@id";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("id", SqlDbType.Int).Value = id;
            sqlUtil.ExecuteNoneQuery(cmd);
        }

    }
}
